package com.xunfang.dao;

import com.xunfang.bean.Emp;
import com.xunfang.utils.DBManager;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class EmpDaoimpt implements EmpDao {


    //插入

    public int insertEmp(Emp emp) throws Exception {
        Connection conn = DBManager.getConn();
        String sql = "insert into emp values(?,?,?,?,?,?,?,?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1, emp.getEmpno());
        ps.setString(2, emp.getEname());
        ps.setString(3, emp.getJob());
        ps.setInt(4, emp.getMgr());
        ps.setObject(5,emp.getHireDate());
        ps.setDouble(6, emp.getSal());
        ps.setDouble(7, emp.getComm());
        ps.setInt(8, emp.getDeptno());
        int i = ps.executeUpdate();
        DBManager.closeAll(conn, ps, null);
        return i;
    }

    //修改
    @Override
    public int updateEmp(Emp emp) throws Exception {
        Connection conn = DBManager.getConn();
        String sql = "update emp set ename = ? , sal = ? where empno = ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, emp.getEname());
        ps.setDouble(2, emp.getSal());
        ps.setInt(3, emp.getEmpno());
        int i = ps.executeUpdate();
        DBManager.closeAll(conn, ps, null);
        return i;
    }

    //删除
    @Override
    public int deleteEmp(int id) throws Exception {
        Connection conn = DBManager.getConn();
        String sql = "delete from emp where empno = ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1, id);
        int i = ps.executeUpdate();
        DBManager.closeAll(conn, ps, null);
        return i;
    }

    //封装全查询的方法
    @Override
    public List<Emp> selectAllEmp() throws Exception {
        Connection conn = DBManager.getConn();
        String sql = "select * from emp";
        PreparedStatement ps = conn.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        List<Emp> list = new ArrayList<>();
        while (rs.next()) {
            //创建Emp对象 - DBUtils框架
            Emp emp = new Emp();
            emp.setEmpno(rs.getInt(1));
            emp.setEname(rs.getString(2));
            emp.setJob(rs.getString(3));
            emp.setMgr(rs.getInt(4));
            emp.setHireDate(rs.getDate(5));
            emp.setSal(rs.getDouble(6));
            emp.setComm(rs.getDouble(7));
            emp.setDeptno(rs.getInt(8));
            list.add(emp);
        }
        DBManager.closeAll(conn, ps, rs);
        return list;
    }

    //超级无敌牛逼  动态查询
    @Override
    public List<Emp> selectEmp(String name, String job, Double sal) throws Exception {
        Connection conn = DBManager.getConn();
        List<Object> result = new ArrayList<>();
        String sql = " select * from emp where 1=1 ";
        if (name != null && !name.equals("")) {
            sql = sql + " and ename like ?";
            result.add("%" + name + "%");
        }
        if (job != null && !job.equals("")) {
            sql = sql + " and job = ? ";
            result.add(job);
        }
        if (sal != null) {
            sql = sql + " and sal = ? ";
            result.add(sal);
        }
        PreparedStatement ps = conn.prepareStatement(sql);
        for (int i = 0; i < result.size(); i++) {
            ps.setObject(i + 1, result.get(i));
        }
        ResultSet rs = ps.executeQuery();
        List<Emp> list = new ArrayList<>();
        while (rs.next()) {
            //创建Emp对象 - DBUtils框架
            Emp emp = new Emp();
            emp.setEmpno(rs.getInt(1));
            emp.setEname(rs.getString(2));
            emp.setJob(rs.getString(3));
            emp.setMgr(rs.getInt(4));
            emp.setHireDate(rs.getDate(5));
            emp.setSal(rs.getDouble(6));
            emp.setComm(rs.getDouble(7));
            emp.setDeptno(rs.getInt(8));
            list.add(emp);
        }
        DBManager.closeAll(conn, ps, rs);
        return list;
    }

    public static List<Emp> moveselect(String ename, String job, Double sal) throws Exception{
        Connection conn = DBManager.getConn();
        String sql = " select * from emp where 1=1 ";
        List rs = new ArrayList();
        if (ename != null && !ename.equals("")){
            sql = sql + " and ename like ? ";
            rs.add("%" + ename + "%");
        }
        if (job != null && !job.equals("")){
            sql = sql + " and job = ? ";
            rs.add(job);
        }
        if (sal != null && !sal.equals("")){
            sql = sql + " and sal = ? ";
            rs.add(sal);
        }

        PreparedStatement ps = conn.prepareStatement(sql);
        for (int i = 0; i < rs.size(); i++) {
            ps.setObject(i+1,rs.get(i));
        }
        ResultSet rst = ps.executeQuery();
        List<Emp> list = new ArrayList<>();
        while (rst.next()){
            Emp emp = new Emp();
            emp.setEmpno(rst.getInt(1));
            emp.setEname(rst.getString(2));
            emp.setJob(rst.getString(3));
            emp.setMgr(rst.getInt(4));
            emp.setHireDate(rst.getDate(5));
            emp.setSal(rst.getDouble(6));
            emp.setComm(rst.getDouble(7));
            emp.setDeptno(rst.getInt(8));
            list.add(emp);
        }
        DBManager.closeAll(conn,ps,rst);
        return list;
    }

    //删除部门表
    public int deleteempEmp(int id) throws Exception {
        Connection conn = DBManager.getConn();
        String sql = "delete from emp where deptno = ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1, id);
        int i = ps.executeUpdate();
        DBManager.closeAll(conn, ps, null);
        return i;
    }

    //删除部门表
    public int deleteDetEmp(int id) throws Exception {
        Connection conn = DBManager.getConn();
        String sql = "delete from dept where deptno = ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1, id);
        int i = ps.executeUpdate();
        DBManager.closeAll(conn, ps, null);
        return i;
    }
}
